// #[warn(dupli)]
// #[allow(diplica)]
use crate::{
    /*types::Db,*/ error::DbError,
    models::{Config, Message, Post, UserPostInfo}, /*queries::{UserPostInfo}*/
};
use serde::Deserialize;
#[cfg(feature = "telegram")]
use teloxide_core::types::UserId;
use time::OffsetDateTime;
use tracing::info;
use uuid::Uuid;

use tokio_stream::StreamExt;
// use tokio_postgres::{Client, NoTls, Connection, Socket, tls::NoTlsStream};
use sqlx::{postgres::PgPoolOptions, FromRow, Pool, Postgres, Row};
#[derive(Clone)]
pub struct Db {
    pool: Pool<Postgres>,
    // connection: Connection<Socket, NoTlsStream>
}
impl Db {
    /// Connect to postgres database
    pub async fn connect(postgres_url: &str) -> Result<Self, DbError> {
        let pool = PgPoolOptions::new()
            .max_connections(10)
            .connect(postgres_url)
            .await?;
        // let (client, connection) = tokio_postgres::connect(
        //     &format!("host={url} user={user} dbname={dbname}"),
        //     NoTls,
        // )
        // .await?;

        // tokio::spawn(async move {
        //     connection
        //         .await
        //         // .context("postgres connection error:")
        //         .unwrap();
        // });

        // let db = client.database("ReadLaterBot");
        Ok(Self {
            pool,
            //  connection: connection
        })
    }
    pub async fn create_post(&self, post: &Post) -> Result<Post, DbError> {
        info!("creating post");
        let statement = r"
     insert into posts (real_url, title, word_count) values ($1, $2, $3)
     returning id, real_url, created, title, word_count, alternative_url
    ";
        // let row = self.client.query_one(statement, &[&id, &real_url]).await?;
        let post = sqlx::query_as::<_, Post>(statement)
            // .bind(id)
            .bind(post.real_url())
            .bind(post.title())
            .bind(post.word_count())
            .fetch_one(&self.pool)
            .await?;
        // let post = Post::builder()
        // .id(row.get(0))
        // .real_url(row.get(1))
        // .created(row.get(2))
        // .title(row.get(3))
        // .word_count(row.get(4))
        // .alternative_url(row.get(5))
        // .build();
        Ok(post)
    }
    pub async fn find_post(&self, real_url: &str) -> Result<Option<Post>, DbError> {
        info!("search for post");
        let statement = r"select id, real_url, created, 
    COALESCE( 
        NULLIF (title, ''),
        title
        ) as title,
    
     word_count, alternative_url
    from posts 
    where real_url = $1";
        // let row = self.client.query_one(statement, &[&id, &real_url]).await?;
        let post = sqlx::query_as::<_, Post>(statement)
            // .bind(id)
            .bind(real_url)
            .fetch_optional(&self.pool)
            .await?;
        // let post = Post::builder()
        // .id(row.get(0))
        // .real_url(row.get(1))
        // .created(row.get(2))
        // .title(row.get(3))
        // .word_count(row.get(4))
        // .alternative_url(row.get(5))
        // .build();
        Ok(post)
    }
    pub async fn create_user_post(
        &self,
        id: Uuid,
        original_url: &str,
        user_id: Uuid,
    ) -> Result<UserPostInfo, DbError> {
        info!("creating user post");
        let statement = r"
     insert into userposts (original_url, post_id, user_id) values ($2, $1, $3)
     returning id, favorite, read, show_real_url
    ";
        let userpost = sqlx::query_as::<_, UserPostInfo>(statement)
            .bind(id)
            .bind(original_url)
            .bind(user_id)
            .fetch_one(&self.pool)
            .await?;
        // let userpost = UserPostInfo { id: row.get(0), favorite: row.get(1), read: row.get(2) };
        Ok(userpost)
    }
    pub async fn find_user_post(
        &self,
        user_id: Uuid,
        post_id: Uuid,
    ) -> Result<Option<UserPostInfo>, DbError> {
        info!("searching for user post");
        let statement = r"
    select (id, favorite, read, show_real_url) from userposts where post_id = $1 and user_id = $2
    ";
        let userpost = sqlx::query_as::<_, UserPostInfo>(statement)
            .bind(user_id)
            .bind(post_id)
            .fetch_optional(&self.pool)
            .await?;
        // let userpost = UserPostInfo { id: row.get(0), favorite: row.get(1), read: row.get(2) };
        Ok(userpost)
    }
    #[cfg(feature = "matrix")]
    pub async fn create_user_matrix(&self, user_id: &str) -> Result<Uuid, DbError> {
        let statement = r"
     insert into users (matrix_id) values ($1) returning id
    ";
        // self.client.execute(statement, &[&id]).await?;
        let id: Uuid = sqlx::query(statement)
            .bind(user_id)
            .fetch_one(&self.pool)
            .await?
            .try_get(0)?;
        Ok(id)
    }
    #[cfg(feature = "telegram")]
    pub async fn create_user_tg(
        &self,
        user_id: &UserId,
        user_name: &Option<String>,
    ) -> Result<Uuid, DbError> {
        info!("creating user");
        let statement = r"
     insert into users (telegram_id, telegram_username) values ($1, $2) returning id
    ";
        // self.client.execute(statement, &[&(id.0 as i64), &username]).await?;
        let id = sqlx::query(statement)
            .bind(user_id.0 as i64)
            .bind(user_name)
            // .bind(username)
            .fetch_one(&self.pool)
            .await?
            .try_get(0)?;
        Ok(id)
    }
    #[cfg(feature = "matrix")]
    pub async fn find_user_matrix(&self, user_id: &str) -> Result<Option<Uuid>, DbError> {
        let statement = r"
    select id from users where matrix_id = $1
    ";
        // self.client.execute(statement, &[&id]).await?;
        let res = sqlx::query(statement)
            .bind(user_id)
            .fetch_optional(&self.pool)
            .await?
            .map(|r| r.get("id"));
        Ok(res)
    }
    #[cfg(feature = "telegram")]
    pub async fn find_user_tg(&self, user_id: &UserId) -> Result<Option<Uuid>, DbError> {
        info!("searching for user");
        let statement = r"
    select id from users where telegram_id = $1
    ";
        // self.client.execute(statement, &[&(id.0 as i64), &username]).await?;
        let res = sqlx::query(statement)
            .bind(user_id.0 as i64)
            // .bind(username)
            .fetch_optional(&self.pool)
            .await?
            .map(|r| r.get("id"));
        Ok(res)
    }
    #[cfg(feature = "telegram")]
    pub async fn get_random_post_tg(
        &self,
        user_id: &UserId,
    ) -> Result<Option<PostRandom>, DbError> {
        info!("getting random post");
        let statement = r"
        SELECT up.id, p.real_url, up.original_url, p.alternative_url, up.favorite, up.show_real_url,
        up.show_alternative_url, 
        COALESCE( 
        NULLIF (p.title, ''),
        NULLIF (p.title, ' '),
        p.title
        ) as title, 
        p.word_count, up.read
        from userposts as up 
        inner join posts as p on up.post_id = p.id
        inner join users as u on u.id = up.user_id
        where up.read = false and u.telegram_id = $1 order by random() limit 1";
        // self.client.query_one(&statement, &[&id.user_id()]).await
        let res = sqlx::query_as::<_, PostRandom>(statement)
            .bind(user_id.0 as i64)
            .fetch_optional(&self.pool)
            .await?;
        // match row {
        Ok(res)
        //     Err(e) => {
        //         None
        //     }
        //     Ok(row) => {
        //         Some(
        //             // PostRandom {
        //             //     real_url: row.get(0.to_owned()),
        //             //     original_url: row.get(1.to_owned()),
        //             //     alternative_url: row.get(2.to_owned()),
        //             //     favorite: row.get(3.to_owned()),
        //             //     show_real_url: row.get(4.to_owned()),
        //             //     show_alternative_url: row.get(5.to_owned()),
        //             //     title: row.get(6),
        //             //     word_count: row.get(7) }
        //             PostRandom::builder()
        //             .real_url(row.get(0))
        //             .original_url(row.get(1))
        //             .alternative_url(row.get(2))
        //             .favorite(row.get(3))
        //             .show_real_url(row.get(4))
        //             .show_alternative_url(row.get(5))
        //             .title(row.get(6))
        //             .word_count(row.get(7))
        //             .build()
        // )
        // }
        // }
    }
    #[cfg(feature = "matrix")]
    pub async fn get_random_post_matrix(
        &self,
        user_id: &str,
    ) -> Result<Option<PostRandom>, DbError> {
        let statement = r"
        SELECT up.id, p.real_url, up.original_url, p.alternative_url, up.favorite, up.show_real_url,
        up.show_alternative_url, 
        COALESCE( 
        NULLIF (p.title, ''),
        NULLIF (p.title, ' '),
        p.title
        ) as title, 
        p.word_count, up.read
        from userposts as up 
        inner join posts as p on up.post_id = p.id
        inner join users as u on u.id = up.user_id
        where up.read = false and u.matrix_id = ? order by random() limit 1";
        // self.client.query_one(&statement, &[&id]).await
        let res = sqlx::query_as::<_, PostRandom>(statement)
            .bind(user_id)
            .fetch_optional(&self.pool)
            .await?;
        // match row {
        Ok(res)
        //     Err(e) => {
        //         None
        //     }
        //     Ok(row) => {
        //         Some(
        //             // PostRandom {
        //             //     real_url: row.get(0.to_owned()),
        //             //     original_url: row.get(1.to_owned()),
        //             //     alternative_url: row.get(2.to_owned()),
        //             //     favorite: row.get(3.to_owned()),
        //             //     show_real_url: row.get(4.to_owned()),
        //             //     show_alternative_url: row.get(5.to_owned()),
        //             //     title: row.get(6),
        //             //     word_count: row.get(7) }
        //             PostRandom::builder()
        //             .real_url(row.get(0))
        //             .original_url(row.get(1))
        //             .alternative_url(row.get(2))
        //             .favorite(row.get(3))
        //             .show_real_url(row.get(4))
        //             .show_alternative_url(row.get(5))
        //             .title(row.get(6))
        //             .word_count(row.get(7))
        //             .build()
        // )
        // }
        // }
    }
    pub async fn archive_post(&self, userpost_id: Uuid) -> Result<(), DbError> {
        info!("archiving post");
        sqlx::query("UPDATE userposts SET read = true WHERE id = $1")
            .bind(userpost_id)
            .execute(&self.pool)
            .await?;
        // db.lock()V
        //     .await
        //     .deref()
        //     .execute(
        //         "UPDATE userposts SET read = true WHERE id = $1",
        //         &[&userpost_id],
        //     )
        //     .await?;

        Ok(())
    }
    pub async fn unarchive_post(&self, userpost_id: Uuid) -> Result<(), DbError> {
        info!("unarchiving post");
        sqlx::query("UPDATE userposts SET read = false WHERE id = $1")
            .bind(userpost_id)
            .execute(&self.pool)
            .await?;
        // db.lock()V
        //     .await
        //     .deref()
        //     .execute(
        //         "UPDATE userposts SET read = true WHERE id = $1",
        //         &[&userpost_id],
        //     )
        //     .await?;

        Ok(())
    }
    pub async fn favorite_post(&self, userpost_id: Uuid) -> Result<(), DbError> {
        sqlx::query("UPDATE userposts SET favorite = true WHERE id = $1")
            .bind(userpost_id)
            .execute(&self.pool)
            .await?;
        // db.lock()V
        //     .await
        //     .deref()
        //     .execute(
        //         "UPDATE userposts SET read = true WHERE id = $1",
        //         &[&userpost_id],
        //     )
        //     .await?;

        Ok(())
    }
    pub async fn unfavorite_post(&self, userpost_id: Uuid) -> Result<(), DbError> {
        sqlx::query("UPDATE userposts SET favorite = false WHERE id = $1")
            .bind(userpost_id)
            .execute(&self.pool)
            .await?;
        // db.lock()V
        //     .await
        //     .deref()
        //     .execute(
        //         "UPDATE userposts SET read = true WHERE id = $1",
        //         &[&userpost_id],
        //     )
        //     .await?;

        Ok(())
    }
    pub async fn set_show_alternative_url(&self, userpost_id: &Uuid) -> Result<(), DbError> {
        sqlx::query(
            "UPDATE userposts SET show_alternative_url = not show_alternative_url WHERE id = $1",
        )
        .bind(userpost_id)
        .execute(&self.pool)
        .await?;
        // db.lock()V
        //     .await
        //     .deref()
        //     .execute(
        //         "UPDATE userposts SET read = true WHERE id = $1",
        //         &[&userpost_id],
        //     )
        //     .await?;

        Ok(())
    }
    pub async fn set_show_real_url(&self, userpost_id: &Uuid) -> Result<(), DbError> {
        sqlx::query("UPDATE userposts SET show_real_url = not show_real_url WHERE id = $1")
            .bind(userpost_id)
            .execute(&self.pool)
            .await?;
        // db.lock()V
        //     .await
        //     .deref()
        //     .execute(
        //         "UPDATE userposts SET read = true WHERE id = $1",
        //         &[&userpost_id],
        //     )
        //     .await?;

        Ok(())
    }
    pub async fn show_alternative_url(&self, userpost_id: Uuid) -> Result<bool, DbError> {
        let res = sqlx::query("SELECT show_alternative_url from userposts WHERE id = $1")
            .bind(userpost_id)
            .fetch_one(&self.pool)
            .await?
            .try_get(0)?;
        // db.lock()V
        //     .await
        //     .deref()
        //     .execute(
        //         "UPDATE userposts SET read = true WHERE id = $1",
        //         &[&userpost_id],
        //     )
        //     .await?;

        Ok(res)
    }
    pub async fn get_alternative_url(&self, userpost_id: Uuid) -> Result<Option<String>, DbError> {
        let res = sqlx::query(
            "SELECT p.alternative_url from posts as p
    inner join userposts as up
    on up.post_id = p.id
    WHERE up.id = $1",
        )
        .bind(userpost_id)
        .fetch_one(&self.pool)
        .await?
        .try_get(0)?;
        // db.lock()V
        //     .await
        //     .deref()
        //     .execute(
        //         "UPDATE userposts SET read = true WHERE id = $1",
        //         &[&userpost_id],
        //     )
        //     .await?;

        Ok(res)
    }
    pub async fn get_real_url(&self, userpost_id: &Uuid) -> Result<Option<String>, DbError> {
        let res = sqlx::query(
            "SELECT p.real_url from posts as p
    inner join userposts as up
    on up.post_id = p.id
    WHERE up.id = $1",
        )
        .bind(userpost_id)
        .fetch_one(&self.pool)
        .await?
        .try_get(0)?;
        // db.lock()V
        //     .await
        //     .deref()
        //     .execute(
        //         "UPDATE userposts SET read = true WHERE id = $1",
        //         &[&userpost_id],
        //     )
        //     .await?;

        Ok(res)
    }
    pub async fn show_real_url(&self, userpost_id: &Uuid) -> Result<bool, DbError> {
        let res = sqlx::query("SELECT show_real_url from userposts WHERE id = $1")
            .bind(userpost_id)
            .fetch_one(&self.pool)
            .await?
            .try_get(0)?;
        // db.lock()V
        //     .await
        //     .deref()
        //     .execute(
        //         "UPDATE userposts SET read = true WHERE id = $1",
        //         &[&userpost_id],
        //     )
        //     .await?;

        Ok(res)
    }
    pub async fn delete_post(&self, userpost_id: Uuid) -> Result<(), DbError> {
        sqlx::query("DELETE FROM userposts WHERE post_id = $1")
            .bind(userpost_id)
            .execute(&self.pool)
            .await?;
        // db.lock()
        //     .await
        //     .deref()
        //     .query(
        //     &[&userpost_id],
        // )
        // .await?;

        Ok(())
    }
    pub async fn is_read(&self, userpost_id: &Uuid) -> Result<bool, DbError> {
        // let res = db
        //     .lock()
        //     .await
        //     .deref()
        //     .query_one(
        let res: (bool,) = sqlx::query_as("SELECT read FROM userposts WHERE id = $1")
            .bind(userpost_id)
            .fetch_one(&self.pool)
            .await?;
        //     &[&userpost_id],
        // )
        // .await?
        // .get(0);
        Ok(res.0)
    }
    pub async fn is_favorite(&self, userpost_id: &Uuid) -> Result<bool, DbError> {
        // let res = db
        //     .lock()
        //     .await
        //     .deref()
        //     .query_one(
        let res: (bool,) = sqlx::query_as("SELECT favorite FROM userposts WHERE id = $1")
            .bind(userpost_id)
            .fetch_one(&self.pool)
            .await?;
        //     &[&userpost_id],
        // )
        // .await?
        // .get(0);
        Ok(res.0)
    }
    pub async fn get_matrix_config(&self) -> Result<Option<Config>, DbError> {
        //     let query = db.clone().lock()
        // .await
        // .query_one(
        let query: Option<Config> =
            sqlx::query_as("select access_token, device_id from matrix_config")
                .fetch_optional(&self.pool)
                .await?; //, &[]).await;
                         // if let Ok(row) = query{
                         //     Some(Config{
                         //         access_token: row.get(0),
                         //         device_id: row.get(1),
                         //     })
                         // } else {
                         //     None
                         // }
        Ok(query)
    }
    pub async fn save_matrix_config(
        &self,
        access_token: &str,
        device_id: &str,
    ) -> Result<(), DbError> {
        sqlx::query("insert into matrix_config (access_token, device_id) values ($1, $2) ")
            .bind(access_token)
            .bind(device_id)
            .execute(&self.pool)
            .await?;
        Ok(())
    }
    pub async fn create_message_event(
        &self,
        userpost_id: Uuid,
        event_id: String,
    ) -> Result<(), DbError> {
        let message = Message {
            event_id,
            userpost_id,
            sent: OffsetDateTime::now_utc(),
        };
        /* ::builder()
        .userpost_id(userpost_id)
        .event_id(event_id)
        .sent(OffsetDateTime::now_utc())
        .build();*/
        // db.lock()
        //     .await
        //     .deref()
        //     .query(
        sqlx::query("INSERT INTO messages (userpost_id, event_id, sent) VALUES ($1, $2, $3)")
            .bind(message.userpost_id())
            .bind(message.event_id())
            .bind(message.sent())
            .execute(&self.pool)
            .await?;
        Ok(())
        //         &[&message.userpost_id(), &message.event_id(), &message.sent()],
        //     )
        //     .await?;
        // Ok(())
    }
    #[cfg(feature = "matrix")]
    pub async fn count_unread_time_db_matrix(&self, user_id: &str) -> Result<i64, DbError> {
        let statement = r"SELECT COALESCE(SUM(up.word_count), 0) as total FROM userposts as up
    inner join users as u on u.id = up.user_id
     WHERE read = false AND u.matrix_id = $1";
        let res: (i64,) = sqlx::query_as(statement)
            .bind(user_id)
            .fetch_one(&self.pool)
            .await?;
        Ok(res.0)
    }
    #[cfg(feature = "telegram")]
    pub async fn count_unread_time_db_tg(&self, user_id: &UserId) -> Result<i64, DbError> {
        let statement = r"SELECT COALESCE(SUM(up.word_count), 0) as total FROM userposts as up
    inner join users as u on u.id = up.user_id
    WHERE read = false and u.telegram_id = $1";
        let res: (i64,) = sqlx::query_as(statement)
            .bind(user_id.0 as i64)
            .fetch_one(&self.pool)
            .await?;
        Ok(res.0)
    }
    #[cfg(feature = "matrix")]
    pub async fn count_unread_posts_db_matrix(&self, user_id: &str) -> Result<i64, DbError> {
        let statement = r"SELECT COALESCE(COUNT(up.id), 0) as total FROM userposts as up
    inner join users as u on u.id = up.user_id
     WHERE read = false AND u.matrix_id = $1";
        let res: (i64,) = sqlx::query_as(statement)
            .bind(user_id)
            .fetch_one(&self.pool)
            .await?;
        Ok(res.0)
    }
    #[cfg(feature = "telegram")]
    pub async fn count_unread_posts_db_tg(&self, user_id: &UserId) -> Result<i64, DbError> {
        info!("counting unread posts");
        // let user_id = user_id.0;
        let statement = r"SELECT COALESCE(COUNT(up.id), 0) as total FROM userposts as up
    inner join users as u on u.id = up.user_id
     WHERE read = false AND u.telegram_id = $1";
        let res: (i64,) = sqlx::query_as(statement)
            .bind(user_id.0 as i64)
            .fetch_one(&self.pool)
            .await?;
        // info!("posts: {}", res.0);
        Ok(res.0)
    }
    #[cfg(feature = "matrix")]
    pub async fn get_user_id_matrix(&self, user_id: &str) -> Result<Uuid, DbError> {
        let res: (Uuid,) = sqlx::query_as("SELECT id FROM users WHERE matrix_id = $1")
            .bind(user_id)
            .fetch_one(&self.pool)
            .await?;
        Ok(res.0)
    }
    #[cfg(feature = "telegram")]
    pub async fn get_user_id_tg(&self, user_id: &UserId) -> Result<Uuid, DbError> {
        let id = user_id.0 as i64;
        let res: (Uuid,) = sqlx::query_as("SELECT id FROM users WHERE telegram_id = $1")
            .bind(id)
            .fetch_one(&self.pool)
            .await?;
        Ok(res.0)
    }
    pub async fn get_userpost_id(&self, event_id: String) -> Result<Option<Uuid>, DbError> {
        // let rows = db
        //     .lock()
        //     .await
        //     .deref()
        let res: (Option<Uuid>,) =
            sqlx::query_as("SELECT userpost_id FROM messages WHERE event_id = $1")
                .bind(event_id)
                .fetch_one(&self.pool)
                .await?;
        Ok(res.0)
    }
}
// use typed_builder::TypedBuilder;
#[derive(Debug, Deserialize, Clone, FromRow)]
pub struct PostRandom {
    pub id: Uuid,
    pub real_url: String,
    pub original_url: String,
    pub alternative_url: Option<String>,
    pub favorite: bool,
    pub show_real_url: bool,
    pub show_alternative_url: bool,
    pub title: Option<String>,
    pub word_count: Option<i64>,
    pub read: bool,
}
// impl PostRandom <'a> {
//     fn new( real_url: &str, original_url: &str, alternative_url: &str, )
// }
